﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookClubIt.DTO;
using System.Data.SqlClient;
using BookClubIt.Data.Commands;
using System.Data;

namespace BookClubIt.Data
{
    public class MemberData
    {
        public Member SaveMember(DTO.Member member)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            SqlCommand command = MemberCommandList.SaveMember;
            command.Parameters["@Id"].Value = member.Id;
            command.Parameters["@Username"].Value = member.Username;
            command.Parameters["@Email"].Value = member.Email;
            command.Parameters["@CreatedDate"].Value = member.CreatedDate;
            command.Parameters["@Biography"].Value = member.Biography;
            command.Parameters["@Zipcode"].Value = member.Zipcode;
            command.Parameters["@AvatarURL"].Value = member.AvatarURL;
            command.Parameters["@NotificationId"].Value = member.NotificationId;
            db.ExecuteNonQuery(command);

            member.Id = Convert.ToInt32(command.Parameters["@Id"].Value);

            return member;
        }

         public void UpdateMemberNotification(int uid, int notifid)
        {
            try
            {
            BciDatabase db = new BciDatabase("Bookclub");
            SqlCommand command = MemberCommandList.UpdateMemberNotification;
            command.Parameters["@UserId"].Value = uid;
            command.Parameters["@NotificationId"].Value = notifid;
            db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }


        public Member GetMemberById(int uid)
        {
            Member member = new Member();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * from member where id = {0}", uid)))
            {
                if (reader.Read())
                {
                    member = BuildMember(reader);
                }
            }
            return member;
        }


        public Member UpdateMemberAvatar(int uid, string filename)
        {
            Member member = new Member();
            BciDatabase db = new BciDatabase("Bookclub");
            string SQLQuery1 = "UPDATE member SET avatarurl = '"+filename+"' WHERE Id = "+uid+"";

            SqlCommand updateavatar = new SqlCommand(SQLQuery1);

            SqlDataReader reader;
            reader = db.ExecuteReader(updateavatar);

            return member;

        }
        public Member GetMemberByUsername(string username)
        {
            Member member = new Member();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * from member where username = '{0}'", username)))
            {
                if (reader.Read())
                {
                    member = BuildMember(reader);
                }
            }
            return member;
        }

        public Member GetBookClubAdminById(int cid)
        {
            Member member = new Member();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT *
                                       FROM Member m 
                                       JOIN Admin_Book_Club abc on m.Id = abc.User_Id
                                       WHERE abc.Book_Club_Id = {0}", cid)))
            {
                if (reader.Read())
                {
                    member = BuildMember(reader);
                }
            }
            return member;
        }

        public Members GetBookClubMembersById(int id)
        {
            Members members = new Members();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT m.* 
                    FROM Member m
                    LEFT JOIN Member_Book_Club  mbc ON m.Id = mbc.Member_Id
                    WHERE mbc.Book_Club_Id = {0} AND mbc.Is_Approved != 0 ORDER BY mbc.Joined_Date", id)))
            {
                while (reader.Read())
                {
                    members.Add(BuildMember(reader));
                }
            }

            return members;
        }
        public Members GetTopBookClubMembersById(int cid)
        {
            Members members = new Members();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT TOP 6 m.* 
                    FROM Member m
                    LEFT JOIN Member_Book_Club  mbc ON m.Id = mbc.Member_Id
                    WHERE mbc.Book_Club_Id = {0} AND mbc.Is_Approved != 0 ORDER BY mbc.Joined_Date", cid)))
            {
                while (reader.Read())
                {
                    members.Add(BuildMember(reader));
                }
            }

            return members;
        }
        public Club_Messages GetMemberMessages(int uid)
        {
            Club_Messages messages = new Club_Messages();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT * FROM Book_Club_Message WHERE Reciever_User_Id = {0}", uid)))
            {
                while (reader.Read())
                {
                    messages.Add(BuildMessage(reader));
                }
            }

            return messages;
        }

        public Members GetPendingBookClubMembersById(int id)
        {
            Members members = new Members();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT m.* 
                    FROM Member m
                    LEFT JOIN Member_Book_Club  mbc ON m.Id = mbc.Member_Id
                    WHERE mbc.Book_Club_Id = {0} AND mbc.Is_Approved = 0", id)))
            {
                while (reader.Read())
                {
                    members.Add(BuildMember(reader));
                }
            }

            return members;
        }

        public Members GetBookClubLeadersById(int id)
        {
            Members members = new Members();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT m.* 
                    FROM Member m
                    LEFT JOIN Leader_Book_Club  lbc ON m.Id = lbc.Member_Id
                    WHERE lbc.Book_Club_Id = {0}", id)))
            {
                while (reader.Read())
                {
                    members.Add(BuildMember(reader));
                }
            }

            return members;
        }

        private Member BuildMember(SqlDataReader reader)
        {
            return new Member
            {
               
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                Status = ConvertDBNull.To<int>(reader["Status_Id"], 0),
                Email = ConvertDBNull.To<string>(reader["Email"], string.Empty),
                Username = ConvertDBNull.To<string>(reader["Username"], string.Empty),
                IPAddress = ConvertDBNull.To<string>(reader["IP_Address"], string.Empty),
                CreatedDate = ConvertDBNull.To<DateTime>(reader["Created_Date"], DateTime.MinValue),
                LastSignInDate = ConvertDBNull.To<DateTime>(reader["Last_Sign_In_Date"], DateTime.MinValue),
                Biography = ConvertDBNull.To<string>(reader["Biography"], string.Empty),
                Zipcode = ConvertDBNull.To<string>(reader["Zipcode"], string.Empty),
                AvatarURL = ConvertDBNull.To<string>(reader["AvatarURL"], string.Empty),
                NotificationId = ConvertDBNull.To<int>(reader["Notification_Id"], 0),
            };
        }

        private Club_Message BuildMessage(SqlDataReader reader)
        {
            return new Club_Message
            {
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                Book_Club_Id = ConvertDBNull.To<int>(reader["Book_Club_Id"], 0),
                Sender_User_Id = ConvertDBNull.To<int>(reader["Sender_User_Id"], 0),
                Reciever_User_Id = ConvertDBNull.To<int>(reader["Reciever_User_Id"], 0),
                Message_Text = ConvertDBNull.To<string>(reader["Message"], string.Empty),
                Sent_Date = ConvertDBNull.To<DateTime>(reader["Sent_Date"], DateTime.MinValue),
            };
        }

    }
}
